#!/usr/bin/python
# coding:utf-8

import datetime


def get_trk_idx_lst():
    sql = """
        SELECT ts_code, idx_name, idx_type, start_date, start_point
            , grid_prcnt, last_date, last_point, pre_date, pre_point
        FROM `idx_trk_cfg`
    """
    return sql


def upd_trk_idx(ts_code, last_date, last_point, pre_date, pre_point):
    sql = f"""
        UPDATE idx_trk_cfg
        SET last_date = \'{last_date}\', last_point = {last_point}, pre_date = \'{pre_date}\', pre_point = {pre_point}
        WHERE ts_code = \'{ts_code}\'
    """
    return sql


def get_trk_idx_daily(ts_code, start_date):
    sql = f"""
        SELECT t.trade_date, t.close
        FROM ts_fundDaily t
        WHERE t.ts_code = \'{ts_code}\'
            AND t.trade_date > \'{start_date}\'
        ORDER BY t.trade_date ASC
    """
    return sql


def get_fund_type(accntId, fundcode):
    sql = f"""
        SELECT fundType
        FROM prcntRule t
        WHERE t.accntId = {accntId}
            AND t.prdctCode = \'{fundcode}\'
    """
    return sql


def get_fund_last_nvdate(fundcode):
    """
    查询指定产品在产品净值表中最新一条净值记录的净值日期
    """
    sql = """
        SELECT nvDate
        FROM fundNv
        WHERE fundcode = \'{fundcode}\'
        ORDER BY nvDate DESC
        LIMIT 1   
    """.format(fundcode=fundcode)
    return sql


def get_fund_last_nvdate2(fundcode):
    """
    查询指定产品在证券交易所行情表中最新一条记录的日期
    """
    sql = f"""
        SELECT trade_date
        FROM ts_fundDaily
        WHERE ts_code = \'{fundcode}\'
        ORDER BY trade_date DESC
        LIMIT 1   
    """
    return sql


def del_last_nvdate2(fundcode, trade_date):
    """
    查询指定产品在证券交易所行情表中最新一条记录的日期
    """
    sql = f"""
        DELETE
        FROM ts_fundDaily
        WHERE ts_code = \'{fundcode}\' and trade_date = \'{trade_date}\'
    """
    return sql


def set_fund_nv(fundcode, nv_date, unit_nv, acc_nv, equity_return):
    """
    查询指定产品在产品净值表中最新一条净值记录的净值日期
    """
    sql = """
        INSERT INTO fundNv (fundCode, nvDate, unitNv, accNv, equityReturn)
        VALUES (\'{fundCode}\', \'{nvDate}\', {unitNv}, {accNv}, {equityReturn})
    """.format(fundCode=fundcode, nvDate=nv_date, unitNv=unit_nv, accNv=acc_nv, equityReturn=equity_return)
    return sql


def get_accnt_asst_dtl(accnt_id):
    sql = """
        SELECT t2.accntId, t2.prdctCode, x.prdctName
            , round(t2.buy_shares - t2.sell_shares, 2) AS leftShares
            , t2.buyAmt, t2.sellAmt
            , round(round(t2.buyAmt - t2.sellAmt, 2) / round(t2.buy_shares - t2.sell_shares, 2), 4) AS costNv
            , round((t2.buy_shares - t2.sell_shares) * round(round(t2.buyAmt - t2.sellAmt, 2) / round(t2.buy_shares - t2.sell_shares, 2), 4), 2) AS costMv
            , s.unitNv
            , round((t2.buy_shares - t2.sell_shares) * s.unitNv, 2) AS crntMv
            , if(round(round(t2.buyAmt - t2.sellAmt, 2) / round(t2.buy_shares - t2.sell_shares, 2), 4) <= 0, -9999, round(s.unitNv / round(round(t2.buyAmt - t2.sellAmt, 2) / round(t2.buy_shares - t2.sell_shares, 2), 4) - 1, 4)) AS prftRng
            , round((s.unitNv - round(round(t2.buyAmt - t2.sellAmt, 2) / round(t2.buy_shares - t2.sell_shares, 2), 4)) * (t2.buy_shares - t2.sell_shares), 2) AS prftAmnt
        FROM (
            SELECT a.*, IFNULL(b.sell_shares, 0) AS sell_shares
                , IFNULL(b.sellAmt, 0) AS sellAmt
            FROM (
                -- 加总购买份额
                SELECT accntId, prdctCode
                    , round(SUM(t.shares), 2) AS buy_shares
                    , round(SUM(t.amount), 2) AS buyAmt
                FROM trdRcd t
                WHERE t.accntId = {accntId}
                    AND t.type = 1
                GROUP BY accntId, prdctCode
            ) a
                LEFT JOIN (
                    -- 加总赎回份额
                    SELECT accntId, prdctCode
                        , round(SUM(t.shares), 2) AS sell_shares
                        , round(SUM(t.amount), 2) AS sellAmt
                    FROM trdRcd t
                    WHERE t.accntId = {accntId}
                        AND t.type = 2
                    GROUP BY accntId, prdctCode
                ) b
                ON a.accntId = b.accntId
                    AND a.prdctCode = b.prdctCode
        ) t2
            LEFT JOIN (
                SELECT s2.*
                FROM fundNv s2, (
                        SELECT fundcode, MAX(nvDate) AS nvDate
                        FROM fundNv
                        GROUP BY fundcode
                    ) s1
                WHERE s2.fundCode = s1.fundCode
                    AND s2.nvDate = s1.nvDate
                UNION
                SELECT s2.ts_code, s2.trade_date, s2.close, s2.close, s2.pct_chg
                FROM ts_fundDaily s2, (
                        SELECT ts_code, MAX(trade_date) AS trade_date
                        FROM ts_fundDaily
                        GROUP BY ts_code
                    ) s1
                WHERE s2.ts_code = s1.ts_code
                    AND s2.trade_date = s1.trade_date
            ) s
            ON t2.prdctCode = s.fundCode
            LEFT JOIN prcntRule x
            ON t2.accntId = x.accntId
                AND t2.prdctCode = x.prdctCode           
    """.format(accntId=accnt_id)
    return sql


def get_accnt_asst2(accnt_id):
    """
    获取账户各项持仓资产的市值、当前占比和占比偏移
    """
    sql = f"""
        SELECT a.accntId, a.prdctCode, a.prdctName, a.mv, a.shares, a.prftRng
            , a.prftAmnt, c.trgtPrcnt, c.asctPrdcts, a.costAmnt
        FROM asstDtl a
            LEFT JOIN prcntRule c
            ON a.accntId = c.accntId
                AND a.prdctCode = c.prdctCode
        WHERE a.accntId = {accnt_id}
        ORDER BY c.asctType, a.prftRng desc
    """
    return sql


def get_accnt_asst(accnt_id, prdct_code):
    """
    获取指定账户的指定持仓产品的市值及盈亏情况
    """
    sql = """
        SELECT t.accntId, t.prdctCode, t.prdctName, t.shares, t.costNv
            , t.costAmnt, t.mv, t.prftRng, t.prftAmnt, t.updDt
        FROM asstDtl t
        WHERE t.accntId = {accntId}
            AND t.prdctCode = \'{prdct_code}\'
    """.format(accntId=accnt_id, prdctCode=prdct_code)
    return sql


def get_accnt_asst_prcnt(accnt_id):
    """
    获取账户各项持仓资产的市值、当前占比和占比偏移
    """
    sql = """
        SELECT a.accntId, a.prdctCode, a.prdctName, a.mv, a.shares, a.prftRng
            , a.prftAmnt, b.ttlMv
            , round(a.mv / b.ttlMv, 4) AS crntPrcnt
            , c.trgtPrcnt
            , round(round(a.mv / b.ttlMv, 4) / c.trgtPrcnt - 1, 4) AS dvtnPrcnt
        FROM asstDtl a
            LEFT JOIN (
                SELECT accntId, round(SUM(mv), 2) AS ttlMv
                FROM asstDtl t
                WHERE t.accntId = {accntId}
                GROUP BY t.accntId
            ) b
            ON a.accntId = b.accntId
            LEFT JOIN prcntRule c
            ON a.accntId = c.accntId
                AND a.prdctCode = c.prdctCode    
    """.format(accntId=accnt_id)
    return sql


def clr_accnt_asst2(accnt_id):
    sql = f"""
        DELETE FROM asstDtl
        WHERE accntId = {accnt_id} 
    """
    return sql


def clr_accnt_asst(accnt_id, prdct_code):
    sql = """
        DELETE FROM asstDtl
        WHERE accntId = {accntId}
            AND prdctCode = \'{prdctCode}\'    
    """.format(accntId=accnt_id, prdctCode=prdct_code)
    return sql


def set_accnt_asst(accnt_id, prdct_code, prdct_name, shares, cost_nv, cost_amnt, mv, prdt_rng, prft_amnt):
    curr_time = datetime.datetime.now()
    date_str = datetime.datetime.strftime(curr_time, '%Y-%m-%d')
    sql = """
        INSERT INTO asstDtl (accntId, prdctCode, prdctName, shares, costNv
            , costAmnt, mv, prftRng, prftAmnt, updDt)
        VALUES ({accntId}, \'{prdctCode}\', \'{prdctName}\', {shares}, {costNv}
            , {costAmnt}, {mv}, {prftRng}, {prftAmnt}, \'{updDt}\')    
    """.format(accntId=accnt_id, prdctCode=prdct_code, prdctName=prdct_name, shares=shares, costNv=cost_nv,
               costAmnt=cost_amnt, mv=mv, prftRng=prdt_rng, prftAmnt=prft_amnt, updDt=date_str)
    return sql
